Skip Headers

Oracle® OLAP DML Reference 10g Release 2 (10.2) Part Number B14346-01 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# COALESCE

The COALESCE function returns the first non-`NA` expression in a list of expressions, or `NA` when all of the expressions evaluate to `NA`.

Return Value

Data type of the first argument.

Syntax

COALESCE (expr [, expr]...)

Arguments

expr

  An expression.

Examples

Example 9-8 Using COALESCE to Determine the Sales Price of a Product

Assume that you have defined the following objects in your analytic workspace. (Note that the `sale` formula uses the COALESCE function for its calculations.)

    DEFINE product_id DIMENSION TEXT
    DEFINE supplier_id DIMENSION TEXT
    DEFINE list_price VARIABLE DECIMAL <product_id supplier_id>
    DEFINE min_price VARIABLE DECIMAL <product_id supplier_id>

    DEFINE sale FORMULA DECIMAL <Product_id supplier_id>
    EQ COALESCE(0.9*list_price, min_price, 5)

The following code illustrates limiting `supplier_id` to a single value and displaying a report that shows the list price, minimum price, and sale price for the products provided by that supplier.

    LIMIT supplier_id TO '102050'
    REPORT DOWN product_id list_price min_price sale

                   ----------SUPPLIER_ID-----------
                   -------------102050-------------
    PRODUCT_ID     LIST_PRICE MIN_PRICE     SALE
    -------------- ---------- ---------- ----------
    2382               850.00     731.00     765.00
    3355                   NA         NA       5.00
    1770                   NA      73.00      73.00
    2378               305.00     247.00     274.50
    1769                48.00         NA      43.20
    1660                16.45      16.45      14.80